package com.maizhi.util;

import jxl.*; 
import jxl.format.Colour;
import jxl.format.UnderlineStyle; 
import jxl.write.*; 
import jxl.write.Number; 
import jxl.write.Boolean; 

import java.io.*; 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Vector;

import javax.servlet.http.HttpServletResponse;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
 
public class xlsUtil
{
	public static BufferedInputStream bis = null;
   	public static BufferedOutputStream bos = null;
	private  File fileWrite=null;
	private OutputStream os=null;
	private File image=null;
	private WritableWorkbook wwb=null; 
	private Workbook wb=null;
	private WritableSheet ws =null;
	private WritableFont wf=null;
	private WritableCellFormat wcf=null;
	private WritableImage wimage=null;
	private WritableCellFormat wcfDF=null;
	private Label label=null;
	private Number labelN=null;
	private NumberFormat nf=null;
	private Number labelNF=null;
	private Boolean labelB=null;
	private DateTime labelDT=null;
	
	public xlsUtil()
	{
	}
  	
  	/** 
     * 创建Excel文件 需要文件路径 以及要创建的工作薄的名字 
     * 和第几个工作薄 excel默认为三个 
     * @param sheetName 
     */ 
     
     public xlsUtil(String filePath,String sheetName,int i) throws Exception
     {
     	fileWrite = new File(filePath);
     	if(fileWrite.exists()){
     		fileWrite.createNewFile();	
     	}else{
     		
     	}
		 
  		/** 
    		* 只能通过API提供的工厂方法来创建Workbook，而不能使用WritableWorkbook的构造函数， 
    		* 因为类WritableWorkbook的构造函数为protected类型 
    		* method(1)直接从目标文件中读取WritableWorkbook wwb = Workbook.createWorkbook(new File(targetfile)); 
    		* method(2)将WritableWorkbook直接写入到输出流 
    		*/ 
          wwb = Workbook.createWorkbook(new FileOutputStream(fileWrite)); 
     	
     	//创建Excel工作表 指定名称和位置 
          ws = wwb.createSheet(sheetName,i);         
     }
   	/** 
     * 在工作文档中建立下一个工作薄
     * @param sheetName 
     * @param i 
     */ 
     public void setSheet(String sheetName,int i) throws Exception
     {
     	wb = Workbook.getWorkbook(fileWrite); 
     	wwb = Workbook.createWorkbook(fileWrite, wb);  
         	ws = wwb.createSheet(sheetName,i);
     }
     
     
    	/** 
     * 读取Excel 读的时候是这样的一个思路,先用一个输入流(InputStream)得到Excel文件,
     * 然后用jxl中的Workbook得到工作薄,
     * 用Sheet从工作薄中得到工作表,用Cell得到工作表中得某个单元格. 
	* InputStream->Workbook->Sheet->Cell,就得到了excel文件中的单元格
     * @param filePath 
     */ 
    public List  readExcel(String filePath) 
    { 
     	List ListExcel = null;
    	  
    	try 
    	{ 
        InputStream is = new FileInputStream(filePath); 
        Workbook rwb = Workbook.getWorkbook(is); 
        
        //Sheet st = rwb.getSheet("0")这里有两种方法获取sheet表,1为名字，而为下标，从0开始 
        Sheet st = rwb.getSheet(0); 
        
        st.getMergedCells();
 
			//列数： 
		  int myCol=st.getColumns(); 
		    //行数： 
 		  int myR=st.getRows(); 
 		
 		  ListExcel =new Vector(); 	
        	  
 		  for(int row=0;row<myR;row++)
 		  {
 			 String[] aExcelData = new String[myCol];
 		  	 for(int col=0;col<myCol;col++)
 		  	 {
 		  	 	aExcelData[col] = st.getCell(col,row).getContents();
 		     }
                
                ListExcel.add(aExcelData);
 		  }
 		  rwb.close(); 
        } 
        catch(Exception e) 
        { 
           ListExcel= null; 
        } 
                    //输出 
 
         return ListExcel; 
    } 

      //**************往工作表中添加数据***************** 
      
     /**
       * 添加Label对象 
       * 
       */
      public void setLable(int column,int row,String str)throws Exception
      {
         		
         label = new Label(column,row,str); 
         ws.addCell(label); 	
      }
      
     /**
       * 添加带有字型Formatting对象  
       * 
       */
      public void setTitleLable(int column,int row,String str,int fontSize)throws Exception
      {
           wf = new WritableFont(WritableFont.ARIAL,fontSize,WritableFont.NO_BOLD,false, 
      			 UnderlineStyle.NO_UNDERLINE,Colour.BLACK); 
           wcf = new WritableCellFormat(wf); 
           wcf.setBackground(Colour.GRAY_25);
           label = new Label(column,row,str,wcf); 
           ws.addCell(label); 	
      }
    
      public void setLable(int column,int row,String str,int fontSize)throws Exception
      {
           wf = new WritableFont(WritableFont.ARIAL,fontSize,WritableFont.NO_BOLD,false, 
      			 UnderlineStyle.NO_UNDERLINE,Colour.BLACK); 
           wcf = new WritableCellFormat(wf);          		
           label = new Label(column,row,str,wcf); 
           ws.addCell(label); 	
      }
      
      /**
       * 指定单元格水平对齐方式  
       * 
       */
      public void setLable(int column,int row,String str,Alignment align)throws Exception
      {
         wf = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false, 
      			 UnderlineStyle.NO_UNDERLINE,Colour.BLACK); 
		 wcf = new WritableCellFormat(wf);
		 wcf.setAlignment(align);
         label = new Label(column,row,str,wcf); 
         ws.addCell(label); 	
      }
      
      /**
       * 指定单元格垂直对齐方式  
       * 
       */
      public void setLable(int column,int row,String str,VerticalAlignment align)throws Exception
      {
         wf = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false, 
      			 UnderlineStyle.NO_UNDERLINE,Colour.BLACK); 
		 wcf = new WritableCellFormat(wf);
		 wcf.setVerticalAlignment(align);
		 label = new Label(column,row,str,wcf); 
          ws.addCell(label); 	
      }
      
     /**
       * 添加带有字体颜色的Formatting对象  
       * 颜色必须是 jxl.format.Colour.RED 类型的  
       * 
       */
      public void setLable(int column,int row,String str,int fontSize,Colour fontColour)throws Exception
      {
           wf = new WritableFont(WritableFont.ARIAL,fontSize,WritableFont.NO_BOLD,false, 
      			 UnderlineStyle.NO_UNDERLINE,fontColour); 
		  wcf = new WritableCellFormat(wf);
          label = new Label(column,row,str,wcf); 
          ws.addCell(label); 	
      }
      
      /**
       * 指定单元格水平对齐方式  
       * 
       */
      public void setLable(int column,int row,String str,int fontSize,Colour fontColour,Alignment align)throws Exception
      {
         wf = new WritableFont(WritableFont.ARIAL,fontSize,WritableFont.NO_BOLD,false, 
      			 UnderlineStyle.NO_UNDERLINE,fontColour); 
		 wcf = new WritableCellFormat(wf);
		 wcf.setAlignment(align);
         label = new Label(column,row,str,wcf); 
         ws.addCell(label); 	
      }
      
      /**
       * 指定单元格垂直对齐方式  
       * 
       */
      public void setLable(int column,int row,String str,int fontSize,Colour fontColour,VerticalAlignment align)throws Exception
      {
         wf = new WritableFont(WritableFont.ARIAL,fontSize,WritableFont.NO_BOLD,false, 
      			 UnderlineStyle.NO_UNDERLINE,fontColour); 
		 wcf = new WritableCellFormat(wf);
		 wcf.setVerticalAlignment(align);
         label = new Label(column,row,str,wcf); 
          ws.addCell(label); 	
      }
      
      /**
       * 指定单元格水平对齐方式  
       * 
       */
      public void setLable(int column,int row,String str,Colour fontColour,Alignment align)throws Exception
      {
         wf = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false, 
      			 UnderlineStyle.NO_UNDERLINE,fontColour); 
		 wcf = new WritableCellFormat(wf);
		 wcf.setAlignment(align);
         label = new Label(column,row,str,wcf); 
         ws.addCell(label); 	
      }
      
      /**
       * 指定单元格垂直对齐方式  
       * 
       */
      public void setLable(int column,int row,String str,Colour fontColour,VerticalAlignment align)throws Exception
      {
         wf = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false, 
      			 UnderlineStyle.NO_UNDERLINE,fontColour); 
		 wcf = new WritableCellFormat(wf);
		 wcf.setVerticalAlignment(align);
         label = new Label(column,row,str,wcf); 
          ws.addCell(label); 	
      }
      
     /**
       * 添加Number对象 type int
       * 
       */
      public void setLableN(int column,int row,int str)throws Exception
      {
  	  	Number labelN = new Number(column,row,str); 
       	ws.addCell(labelN);	
      }
      
     /**
       * 添加Number对象 type long
       * 
       */
      public void setLableN(int column,int row,long str)throws Exception
      {
  	  labelN = new Number(column,row,str); 
       ws.addCell(labelN);	
      }
      
     /**
       * 添加Number对象 type float
       * 
       */
      public void setLableN(int column,int row,float str)throws Exception
      {
  	  labelN = new Number(column,row,str); 
       ws.addCell(labelN);	
      }
      
     /**
       * 添加Number对象 type float is formatting type #.##
       * 
       */
      public void setLableN(int column,int row,float str,NumberFormat wcfN)throws Exception
      {
      	wcfDF = new WritableCellFormat(wcfN); 	
          labelNF = new Number(column,row,str,wcfDF); 
          ws.addCell(labelNF); 	
      }
      
     /**
       * 添加Number对象 type double 
       * 
       */
      public void setLableN(int column,int row,double str)throws Exception
      {
         labelNF = new Number(column,row,str); 
         ws.addCell(labelNF); 	
      }
      
     /**
       * 添加Number对象 type double is formatting  type #.##
       * 
       */
      public void setLableN(int column,int row,double str,NumberFormat wcfN)throws Exception
      {
       
        wcfDF = new WritableCellFormat(wcfN); 	
       labelNF = new Number(column,row,str,wcfDF); 
       ws.addCell(labelNF); 	
      }
      
     /**
       * 添加Boolean对象
       * 
       */
      public void setLabelB(int column,int row,boolean str)throws Exception
      {
        labelB = new Boolean(column,row,str); 
        ws.addCell(labelB); 	
      }
      
     /**
       * 添加DateTime对象 
       * 
       */
      public void setLabelDT(int column,int row,Date str)throws Exception
      {
        labelDT = new DateTime(column,row,str); 
        ws.addCell(labelDT); 
      }
      
     /**
       * 添加DateTime对象 
       * 添加带有formatting的DateFormat对象
       */
      public void setLabelDT(int column,int row,Date str,DateFormat dateType)throws Exception
      {
 
       WritableCellFormat wcfDF = new WritableCellFormat(dateType); 
       DateTime labelDTF = new DateTime(column,row,str,wcfDF); 
       ws.addCell(labelDTF); 
      
      }
      
     /**
       * 添加图片对象,jxl只支持png格式图片 
       * 0,1分别代表x,y.2,2代表宽和高占的单元格数 
       */
      public void setLabelImage(int column,int row,int width,int high,String imagePath)throws Exception
      {

        image = new File(imagePath); 
        wimage = new WritableImage(column,row,width,high,image);//0,1分别代表x,y.2,2代表宽和高占的单元格数 
        ws.addImage(wimage); 
      }
      
     /**
       * 合并单元格
       * 合并第一列第一行到第六列第一行的所有单元格
       * sheet.mergeCells(0,0,5,0); 合并既可以是横向的，
       * 也可以是纵向的。合并后的单元格不能再次进行合并，否则会触发异常。 
       * 
       *
       */
      public void mergeCells(int m,int n,int p,int q)throws Exception
      {
      	ws.mergeCells( m, n, p, q);
      	
      }	
     /**
       *  作用是指定第i+1行的高度，比如：
       *  将第一行的高度设为200
       *  sheet.setRowView(0,200); 
       */
      public void setRowView(int i,int height)throws Exception
      {
      	ws.setRowView( i, height);
      }	 
      
    //  wc.setBackground(<
       
     /**
       *  作用是指定第i+1列的宽度，比如：
       *  将第一列的宽度设为30 
       *  sheet.setColumnView(0,30);  
       */
      public void setMzRowView(int i,int width)throws Exception
      {
      	ws.setColumnView( i, width);
      }	
       
     /**
       * 写入工作表
       *
       */
      public void write()throws Exception
      {
    	try{  
      	 wwb.write(); 
    	}catch(Exception e){ new  Exception ("写入失败 mzExcel.write()"+e.getMessage());}
      }
      
     /**
       * 关闭工作表
       *
       */
      public void close()throws Exception
      {
      	 
      	try{  
         	 if(wwb!=null)
         	 {
         		wwb.close(); 
         		wwb = null;
         	 }   
       	}catch(Exception e){ wwb = null; new  Exception ("关闭失败 mzExcel.close()"+e.getMessage());}
      }
      public File getFileWrite()
      {
    	  return fileWrite;
      }
     /**
       * 写入字符流
       *
       */
      public void setJspExcel(String fileName,HttpServletResponse httpResponse)throws Exception
      {
    		httpResponse.reset();
        	
   		fileName = new String(fileName.getBytes("GBK"),"ISO8859_1");       
 		
 		httpResponse.setContentType("application/vnd.ms-excel");
        	httpResponse.setHeader("Content-disposition", "attachment; filename=" + fileName);
    		
    		bis = new BufferedInputStream(new FileInputStream(fileWrite.toString()));
        	bos = new BufferedOutputStream(httpResponse.getOutputStream());
       	
       	byte[] buff = new byte[20480];
        	int bytesRead;
        
        	while(-1 != (bytesRead = bis.read(buff, 0, buff.length))) 
        	{
          	bos.write(buff,0,bytesRead);
        	}
      }
      
      public void setJspExcel(String filePath,String fileName,HttpServletResponse httpResponse)throws Exception
      {
    		httpResponse.reset();
        	httpResponse.setContentType("application/vnd.ms-excel");
        	httpResponse.setHeader("Content-disposition", "attachment; filename=" + fileName);
    		bis = new BufferedInputStream(new FileInputStream(filePath));
        	bos = new BufferedOutputStream(httpResponse.getOutputStream());
        	byte[] buff = new byte[20480];
        	int bytesRead;
        
        	while(-1 != (bytesRead = bis.read(buff, 0, buff.length))) 
        	{
          	bos.write(buff,0,bytesRead);
        	}
      }
}      
 